package kr.co.doriclassic.summary_java.dao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;

import kr.co.doriclassic.mylibrary.DbUtil;
import kr.co.doriclassic.summary_java.dto.CodeTitle;
import kr.co.doriclassic.summary_java.dto.Department;

public class CodeTitleDao implements InterfaceDao<CodeTitle> {
	private Connection connection;
	private PreparedStatement pstmt;
	
	public CodeTitleDao(Connection connection) {
		this.connection = connection;
	}

	@Override
	public void insertItem(CodeTitle item) throws SQLException {
		String sql = "insert into code_title values(?,?)";
		pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getTitleNo());
		pstmt.setString(2, item.getTitleName());
		pstmt.executeUpdate();
		DbUtil.close(pstmt);			
	}

	@Override
	public void deleteItembyNo(CodeTitle item) throws SQLException {
		String sql = "delete from code_title where titleno = ?";
		pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getTitleNo());
		pstmt.executeUpdate();
		DbUtil.close(pstmt);		
	}

	@Override
	public void updateItembyNo(CodeTitle item) throws SQLException {
		String sql = "update code_title set titlename=? where titleno=?";
		pstmt = connection.prepareStatement(sql);
		pstmt.setString(1, item.getTitleName());
		pstmt.setInt(2, item.getTitleNo());
		pstmt.executeUpdate();
		DbUtil.close(pstmt);		
	}

	@Override
	public ArrayList<CodeTitle> listItems() throws SQLException {
		ArrayList<CodeTitle> list = new ArrayList<>();
		String sql = "select titleno, titlename from code_title";
		pstmt = connection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();

		while(rs.next()){
			list.add(new CodeTitle(
					rs.getInt("titleno"), 
					rs.getString("titlename")));
		}
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return list;
	}

	@Override
	public ArrayList<CodeTitle> searchList(String item) throws SQLException {
		ArrayList<CodeTitle> list = new ArrayList<>();
		String sql = "select titleno, titlename "
				+ "from code_title "
				+ "where titleno regexp ? or titlename regexp ?";
		pstmt = connection.prepareStatement(sql);
		pstmt.setString(1, item);
		pstmt.setString(2, item);
		ResultSet rs = pstmt.executeQuery();
		
		if(rs.next()){
			list.add(new CodeTitle(
					rs.getInt("titleno"),
					rs.getString("titlename")));			
		}
		return list;
	}

	@Override
	public void prnItemList(Comparator<CodeTitle> comp) throws SQLException {
		ArrayList<CodeTitle> list=listItems();
		if(comp == null){
			Collections.sort(list);			
		}
		else{
			Collections.sort(list, comp);
		}
		for(CodeTitle emp : list){
			System.out.println(emp);
		}				
	}

	@Override
	public CodeTitle searchItem(CodeTitle item) throws SQLException {
		CodeTitle codetitle = null;
		String sql = "select titleno, titlename from code_title where titleno=?";
		pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getTitleNo());
		ResultSet rs = pstmt.executeQuery();
		
		if(rs.next()){
			codetitle = new CodeTitle();
			codetitle.setTitleNo(rs.getInt(1));
			codetitle.setTitleName(rs.getString(2));
		}

		return codetitle;
	}

}
